package starkuz.lg.dao.impl;


import starkuz.lg.dao.ifaces.OrderDao;
import starkuz.lg.model.Order;
import org.apache.commons.dbcp.BasicDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Repository
public class OrderDaoImpl implements OrderDao {

    private static final String INSERT_ORDER =
            "insert into orders (siteId, name, email, phone, city, comment, dateMade) values (?, ?, ?, ?, ?, ?, ?)";
    private static final String SELECT_ALL_ORDERS = "select * from orders";
    private static final String SELECT_ALL_ORDERS_BY_SITE_ID = "select * from orders where siteId = ?";
    private static final String SELECT_ALL_ORDERS_BY_USER_ID =
            "select * from orders ord join order_sites sites on ord.siteId = sites.id join users on users.id = sites.ownerId where users.id = ?";
    private static final String COUNT_ALL_ORDERS_BY_SITE_ID = "select count(*) value from orders where siteId = ?";
    private static final String COUNT_ALL_ORDERS_BY_USER_ID = "join!!!";   //todo: реализовать джойн

    @Autowired
    private BasicDataSource ds;

    public void setDs(BasicDataSource ds) {
        this.ds = ds;
    }

    @Override
    public void addOrder(Order order) {
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con =  ds.getConnection();
            psql = con.prepareStatement(INSERT_ORDER);
            psql.setInt(1, order.getSiteId());    //siteId, name, email, phone, city, comment, dateMade
            psql.setString(2, order.getName());
            psql.setString(3, order.getEmail());
            psql.setString(4, order.getPhone());
            psql.setString(5, order.getCity());
            psql.setString(6, order.getComment());
            psql.setDate(7, new Date(new java.util.Date().getTime()));
            psql.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public List<Order> getAllOrders() {
        List<Order> orders = null;
        Connection con = null;
        Statement st = null;
        ResultSet res = null;
        try {
            con = ds.getConnection();
            st = con.createStatement();
            res = st.executeQuery(SELECT_ALL_ORDERS);
            orders = resultSetToOrdersList(res);
            return orders;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                res.close();
                st.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return new ArrayList<Order>();
    }


    @Override
    public List<Order> getAllOrdersBySiteId(Integer siteId) {
        List<Order> orders = null;
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(SELECT_ALL_ORDERS_BY_SITE_ID);
            psql.setInt(1, siteId);
            orders = resultSetToOrdersList(psql.executeQuery());
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return orders;
    }

    @Override
    public List<Order> getAllOrdersByUserId(Integer userId) {
        List<Order> orders = null;
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(SELECT_ALL_ORDERS_BY_USER_ID);
            psql.setInt(1, userId);
            orders = resultSetToOrdersList(psql.executeQuery());
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return orders;
    }

    @Override
    public Integer countOrderBySiteId(Integer siteId) {
        Integer value = null;
        Connection con = null;
        PreparedStatement psql = null;
        ResultSet rs = null;
        try {
            con = ds.getConnection();
            try {
                psql = con.prepareStatement(COUNT_ALL_ORDERS_BY_SITE_ID);
                psql.setInt(1, siteId);
                rs = psql.executeQuery();
                while (rs.next()) {
                    value = rs.getInt("value");
                }
                rs.close();
            } catch (SQLException s) {
                s.printStackTrace();
            }
            con.close();
            rs.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return value;
    }

    @Override
    public Integer countOrderByUserId(Integer siteId) {
        Integer value = null;
        Connection con = null;
        PreparedStatement psql = null;
        ResultSet rs = null;
        try {
            con = ds.getConnection();
            try {
                psql = con.prepareStatement(COUNT_ALL_ORDERS_BY_USER_ID);
                psql.setInt(1, siteId);
                rs = psql.executeQuery();
                while (rs.next()) {
                    value = rs.getInt("value");
                }
                rs.close();
            } catch (SQLException s) {
                s.printStackTrace();
            }
            con.close();
            rs.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return value;
    }

    private List<Order> resultSetToOrdersList(ResultSet rs) throws SQLException {
        List<Order> orders = new ArrayList<Order>();
        while (rs.next()) {
            Order order = new Order();
            order.setId(rs.getInt("id"));
            order.setSiteId(rs.getInt("siteId"));
            order.setName(rs.getString("name"));
            order.setEmail(rs.getString("email"));
            order.setPhone(rs.getString("phone"));
            order.setCity(rs.getString("city"));
            order.setComment(rs.getString("comment"));
            order.setDateMade(rs.getString("dateMade"));

            orders.add(order);
        }
        return orders;
    }
}
